Create Database OnlineSMS

Go
-------------------------------------------------------
Use OnlineSMS

Go
------------------------------------------------------
Create Table [User]
(
	UserID int primary key identity(1,1),
    U_fullname varchar(100) not null,
	U_username varchar(50) unique not null,
	U_password  varchar(50) not null,
	U_email varchar(50) unique not null,
	U_mobile varchar(10) unique not null,
	U_gender bit,	
	U_DOB datetime,
	U_address varchar(200) not null,
	Marital_status bit,
	U_hobbies varchar(50),
	U_sports varchar(50),
	U_image varchar(50),
	U_qualification varchar(50),
	U_school varchar(50),
	U_college varchar(50),
	U_work_status varchar(100),
	U_organization varchar(100),
	U_designation varchar(100)
)

Go
----------------------------------------------------------

Create Table Invitation
(
	InvitationID int primary key Identity(1,1) ,
	UserID int Foreign key references [User](UserID),
	FriendAccountID int,
	I_Status varchar(20),
	I_timestamp smalldatetime default getdate(),	
)

Go
-----------------------------------------------------------

Create Table [MessageToFriend]
(
	MessageID int primary key Identity(1,1) ,
	UserID int Foreign key references [User](UserID),
	M_ToUser int,
	M_detail varchar(200) not null,
	M_status bit,
	M_timestamp smalldatetime default getdate()
)

-------------------------------------------------------------
go

Create Table [MessageToMobile]
(
	MessageID int primary key Identity(1,1) ,
	UserID int Foreign key references [User](UserID),
	M_ToMobile varchar(10) ,
	M_detail varchar(200) not null,
	M_timestamp smalldatetime default getdate(),

)

Go
-------------------------------------------------------------
Create table Friend
(
	FriendID int primary key Identity(1,1) ,
    UserID int Foreign key references [User](UserID),
	FriendAccountID int,
)

-------------------------------------------------------------
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Nguyen Tuan sang','sangnt','123456','sangnt_c00403@fpt.aptech.ac.vn','0975281806',1,'08/09/1990','Bac Ninh','../images/sangnt.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Nguyen Son Tung','tungns','123456','tungns_c00377@fpt.aptech.ac.vn','0975281801',1,'10/22/1989','Ha Noi','../images/tungns.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Tong van Quang','quangtv','123456','quangtv_c00398@fpt.aptech.ac.vn','0975281802',1,'04/02/1985','Ninh Binh','../images/quangtv.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('NGuyen Thi Ly','lynt','123456','lynt_c00428@fpt.aptech.ac.vn','0975281803',0,'05/18/1990','Thai Binh','../images/lynt.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Angela Baby ','baby','123456','asd@fpt.aptech.ac.vn','0975281809',0,'01/08/1990','China','../images/baby.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Bar Rafaeli','barafa','123456','lynt1_c00428@fpt.aptech.ac.vn','0975261803',0,'05/18/1990','Isarel','../images/bar.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Kim Tea Hee','kimteahee','123456','lynt2_c00428@fpt.aptech.ac.vn','0912281803',0,'11/18/1990','Korea','../images/kimth.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Song Hye Kyo','songkyo','123456','lynt3_c00428@fpt.aptech.ac.vn','0975289903',0,'05/01/1989','Korea','../images/shkyo.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Tom Cruise','tomcruise','123456','lynt4_c00428@fpt.aptech.ac.vn','0975999903',1,'05/18/1970','USA','../images/tomcrui.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Ton Phi Phi','phiphi','123456','lynt5_c00428@fpt.aptech.ac.vn','0975281888',0,'05/08/1980','China','../images/tonpp.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('George Clooney','clooney','123456','lynt6_c00428@fpt.aptech.ac.vn','0933281803',1,'07/18/1990','USA','../images/clooney.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Irina Shark','irina','123456','lynt7_c00428@fpt.aptech.ac.vn','0975222803',0,'05/01/1890','Rusia','../images/irina.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Leonado Dicaprio','leonado','123456','lynt8_c00428@fpt.aptech.ac.vn','0976661803',1,'09/18/1990','Italy','../images/leonado.jpg')
insert into [User](U_fullname,U_username,U_password,U_email,U_mobile,U_gender,U_DOB,U_address,U_image) values('Megan Fox','meganfox','123456','megan@hotmail.com','0912345676',0,'01/11/1986','USA','../images/meganfox.jpg')

---------------------------------------
go

create proc sp_GetByUsername
@username varchar(50)
as
begin
	select * from [User] where U_username=@username  
end

---------------------------------------
go

create proc sp_GetFriendByUserID
@userid int
as
begin
select Friend.FriendAccountID as FriendID,[User].U_username as Username from Friend  inner join [user]  on
[User].UserID=Friend.FriendAccountID and Friend.UserID=@userid
end

-------------------------------------
go

create proc sp_SendMessageToMobile
@userid int,
@mobile varchar(10),
@detail varchar(200)
as
begin
	insert into [MessageToMobile](UserID,M_ToMobile,M_detail) values(@userid,@mobile,@detail)
end

-----------------------------------
go

create proc sp_SendMessageToFriend
@userid int,
@friendid int,
@detail varchar(200),
@status bit
as
begin
	insert into [MessageToFriend](UserID,M_ToUser,M_detail,M_Status) values(@userid,@friendid,@detail,@status)
end

-------------------------------
go
create proc sp_ShowMessagePenDing
@userid int
as
begin
	select MessageID,U_username as username,M_detail as detail,M_status,M_timestamp from MessageToFriend join [User] on [User].UserID=MessageToFriend.UserID and MessageToFriend.M_ToUser=@userid and MessageToFriend.M_Status='0'
end

---------------------------
go


create proc sp_ShowMessage
@userid int
as
begin
	select MessageID,U_username as username,M_detail as detail,M_status,M_timestamp from MessageToFriend join [User] on [User].UserID=MessageToFriend.UserID and MessageToFriend.M_ToUser=@userid 
end
--------------------------------

go

create proc sp_ShowInvitationPenDing
@userid int
as
begin
	select InvitationID,Invitation.UserID as UserID,U_username as username,I_status,I_timestamp from Invitation join [User] on [User].UserID=Invitation.UserID and Invitation.FriendAccountID=@userid and Invitation.I_Status='pending'
end
--------------------------------
go
create proc sp_ShowInvitation
@userid int
as
begin
	select InvitationID,Invitation.UserID as UserID,U_username as username,I_status,I_timestamp from Invitation join [User] on [User].UserID=Invitation.UserID and Invitation.FriendAccountID=@userid
end
-----------------------------
go

Create proc sp_SendInvite
@userid int,
@toUser int,
@status varchar(20)
as
begin
	insert into [Invitation](UserID,FriendAccountID,I_Status) values(@userid,@toUser,@status)
end
-----------------------------
go
Create proc sp_AcceptInvite
@id int,
@userid int,
@friendid int
as
begin
	update Invitation set I_Status='accept' where InvitationID=@id
	insert into Friend(UserID,FriendAccountID) values(@friendid,@userid)
	insert into Friend(UserID,FriendAccountID) values(@userid,@friendid)
end
------------------------------
go
Create proc sp_DeleteInvite
@id int
as
begin
	delete Invitation where InvitationID=@id
end

------------------------------
go
Create proc sp_Register
@fullname varchar(100),
@username varchar(50),
@password varchar(50),
@email varchar(50),
@mobile varchar(10),
@gender bit,
@dob datetime,
@address varchar(200)
as
begin
	insert into [User](U_fullname,U_username,U_password,U_email,U_mobile, U_gender,U_DOB,U_address) values(@fullname,@username,@password,@email,@mobile,@gender,@dob,@address)
end

------------------------------
go
Create proc sp_UpdateStatus
@id int
as
begin
	update MessageToFriend set M_Status='1' where MessageID=@id
end

------------------------------
go
Create proc sp_UpdatePassword
@username varchar(50),
@pass varchar(50)
as
begin
	update [user] set U_password=@pass where U_username=@username
end
------------------------------
go
Create proc sp_ChangePassword
@username varchar(50),
@pass varchar(50)
as
begin
	update [user] set U_password=@pass where U_username=@username
end


------The enD-----------------------
------------------------------------
